package common.main.google;

import org.apache.log4j.Logger;

import java.io.UnsupportedEncodingException;
import java.nio.ByteBuffer;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SaveDataToSql {


    private static Logger logger = Logger.getLogger(SaveDataToSql.class);

    public static void insertGooglePaperSearchTopK(List<GooglePaperData> list) {

        try {
            //Class.forName("com.mysql.cj.jdbc.Driver").newInstance();

            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String databaseHost = Config.mysqlHost;//
            String databaseName = Config.mysqlDatabaseName;// 已经在MySQL数据库中创建好的数据库。
            String userName = Config.mysqlUserName;// MySQL默认的root账户名
            String password = Config.mysqlPassword;// 默认的root账户密码为空
            String url ="jdbc:mysql://"+databaseHost+"/"    //autoReconnect=true&characterEncoding=utf-8
                    +databaseName+"?user="+userName+"&password="+password+"&autoReconnect=true&characterEncoding=UTF-8&useSSL=false";
            //Connection conn = DriverManager.getConnection("jdbc:mysql://"+databaseHost+"/" + databaseName, userName, password);
            Connection conn = DriverManager.getConnection(url);

            Statement stmt = conn.createStatement();

            String sql_model = "insert into dataSetgooglePaper_nlp_origin(title,titleUrl,orderId,authors,authorsUrl,brief,pubYear,searchKeyword,authorAffNo,affName,articleKeywords,journal,md5,`references`,citeNum,authorsIndexH,yearsCite,topic,paperClass) " +
                    "values ('<title>','<titleUrl>',<orderId>,'<authors>','<authorsUrl>','<brief>',<pubYear>,'<searchKeyword>','<authorAffNo>','<affName>','<articleKeywords>','<journal>','<md5>','<references>','<citeNum>','<authorsIndexH>','<yearsCite>','<topic>','<paperClass>')";

            // 创建数据库中的表，

            for(GooglePaperData data:list) {

                String sql = sql_model.replace("<title>",data.getTitle().replace("'","\\'")+"")
                        .replace("<titleUrl>",data.getUrl().replace("'","\\'")+"")
                        .replace("<orderId>",data.getOrderId()+"")
                        .replace("<authors>",data.getAuthors().replace("'","\\'")+"")
                        .replace("<authorsUrl>",data.getAuthorsUrl().replace("'","\\'")+"")
                        .replace("<brief>",data.getBrief().replace("'","\\'").replace("\n"," ")+"")
                        .replace("<pubYear>",data.getPubYear()+"")
                        .replace("<searchKeyword>",data.getSearchKeyword().replace("'","\\'")+"")
                        .replace("<authorAffNo>",data.getAuthorAffNo().replace("'","\\'"))
                        .replace("<affName>",data.getAffName().replace("'","\\'"))
                        .replace("<articleKeywords>",data.getArticleKeywords().replace("'","\\'"))
                        .replace("<journal>",data.getJournal().replace("'","\\'"))
                        .replace("<md5>",data.getMd5())
                        .replace("<references>",data.getReferences().replace("\\","\\\\").replace("'","\\'").replace("\n",""))
                        .replace("<citeNum>",data.getCiteNum() + "")
                        .replace("<authorsIndexH>",data.getAuthorsIndexH().replace("'","\\'"))
                        .replace("<yearsCite>",data.getYearsCite())
                        .replace("<topic>",ScholarPaperListStartMain.topicName.replace("'","\\'"))
                        .replace("<paperClass>",data.getPaperClass().replace("'","\\'"));

                try {

                    //sql = new String(sql.getBytes("gbk"),"utf-8");
                    //sql = filterOffUtf8Mb4(sql);
                    stmt.executeUpdate(sql);
                }catch (Exception e) {
                    logger.info("insert sql:"+sql);
                    e.printStackTrace();
                }
            }
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void insertGooglePaperYearsCite(List<GooglePaperData> list) {

        try {
            //Class.forName("com.mysql.cj.jdbc.Driver").newInstance();

            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String databaseHost = Config.mysqlHost;//
            String databaseName = Config.mysqlDatabaseName;// 已经在MySQL数据库中创建好的数据库。
            String userName = Config.mysqlUserName;// MySQL默认的root账户名
            String password = Config.mysqlPassword;// 默认的root账户密码为空
            String url ="jdbc:mysql://"+databaseHost+"/"
                    +databaseName+"?user="+userName+"&password="+password+"&useUnicode=true&characterEncoding=UTF-8&useSSL=false";
            //Connection conn = DriverManager.getConnection("jdbc:mysql://"+databaseHost+"/" + databaseName, userName, password);
            Connection conn = DriverManager.getConnection(url);

            Statement stmt = conn.createStatement();

            String sql_model = "insert into googlePaperYearsCite(title,authors,pubYear,citeNum,citeUrl,yearsCite,searchKeyword) " +
                    "values ('<title>','<authors>',<pubYear>,<citeNum>,'<citeUrl>','<yearsCite>','<searchKeyword>')";

            // 创建数据库中的表，

            for(GooglePaperData data:list) {

                String sql = sql_model.replace("<title>",data.getTitle().replace("'","\\'"))
                        .replace("<authors>",data.getAuthors().replace("'","\\'"))
                        .replace("<pubYear>",data.getPubYear()+"")
                        .replace("<citeNum>",data.getCiteNum()+"")
                        .replace("<citeUrl>",data.getCiteNumUrl()+"")
                        .replace("<yearsCite>",data.getYearsCite())
                        .replace("<searchKeyword>",data.getSearchKeyword());


                try {

                    //sql = new String(sql.getBytes("gbk"),"utf-8");
                    //sql = filterOffUtf8Mb4(sql);
                    stmt.executeUpdate(sql);
                }catch (Exception e) {
                    logger.info("insert sql:"+sql);
                    e.printStackTrace();
                }
            }
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    private  static  String filterOffUtf8Mb4(String text) throws UnsupportedEncodingException {
        byte[] bytes = text.getBytes("UTF-8");
        ByteBuffer buffer = ByteBuffer.allocate(bytes.length);
        int i = 0;
        while (i < bytes.length) {
            short b = bytes[i];
            if (b > 0) {
                buffer.put(bytes[i++]);
                continue;
            }
            b += 256;
            if ((b ^ 0xC0) >> 4 == 0) {
                buffer.put(bytes, i, 2);
                i += 2;
            }
            else if ((b ^ 0xE0) >> 4 == 0) {
                buffer.put(bytes, i, 3);
                i += 3;
            }
            else if ((b ^ 0xF0) >> 4 == 0) {
                i += 4;
            }
        }
        buffer.flip();
        return new String(buffer.array(), "utf-8");
    }

    //获取近一个月的md5
    public static String getAllGooglePaperSearchTopKMD5() throws SQLException {
        String sql = "SELECT md5 FROM dataSetgooglePaper_nlp_origin WHERE insertTime > date_sub(curdate(),interval 1 month);";
        //String sql = "SELECT md5 FROM dataSetgooglePaper_nlp_origin";
        String databaseHost = Config.mysqlHost;//
        String databaseName = Config.mysqlDatabaseName;// 已经在MySQL数据库中创建好的数据库。
        String userName = Config.mysqlUserName;// MySQL默认的root账户名
        String password = Config.mysqlPassword;// 默认的root账户密码为空
        String url ="jdbc:mysql://"+databaseHost+"/"    //autoReconnect=true&characterEncoding=utf-8
                +databaseName+"?user="+userName+"&password="+password+"&autoReconnect=true&characterEncoding=UTF-8&useSSL=false";
        Connection conn = DriverManager.getConnection(url);
        Statement st = conn.createStatement();
        ResultSet rl = st.executeQuery(sql);
        StringBuilder sb = new StringBuilder();
        while (rl.next()){
            sb.append(rl.getString("md5") + ",");
        }
        return sb.toString();
    }

    public static void insertAuthorInfo(List<AuthorInfo> list){

        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String databaseHost = Config.mysqlHost;//
            String databaseName = Config.mysqlDatabaseName;// 已经在MySQL数据库中创建好的数据库。
            String userName = Config.mysqlUserName;// MySQL默认的root账户名
            String password = Config.mysqlPassword;// 默认的root账户密码为空
            String url ="jdbc:mysql://"+databaseHost+"/"
                    +databaseName+"?user="+userName+"&password="+password+"&useUnicode=true&characterEncoding=UTF-8";
            //Connection conn = DriverManager.getConnection("jdbc:mysql://"+databaseHost+"/" + databaseName, userName, password);
            Connection conn = DriverManager.getConnection(url);

            Statement stmt = conn.createStatement();

            String sql_model = "insert into authorInfo" +
                    "(authorName,authorUrl,authorSort,affiliation,hIndex,authorYearsCite,coAuthors,articleName,articleUrl,searchKeyword,md5) " +
                    "values ('<authorName>','<authorUrl>',<authorSort>,'<affiliation>',<hIndex>,'<authorYearsCite>','<coAuthors>','<articleName>','<articleUrl>','<searchKeyword>','<md5>')";

            // 创建数据库中的表，

            for(AuthorInfo data:list) {

                String sql = sql_model.replace("<authorName>",data.getAuthorName().replace("'","\\'"))
                        .replace("<authorUrl>",data.getAuthorUrl().replace("'","\\'"))
                        .replace("<authorSort>",data.getAuthorSort() + "")
                        .replace("<affiliation>",data.getAffiliation().replace("'","\\'") + "")
                        .replace("<hIndex>",data.gethIndex() + "")
                        .replace("<authorYearsCite>",data.getAuthorYearsCite())
                        .replace("<coAuthors>",data.getCoAuthors().replace("'","\\'"))
                        .replace("<articleName>",data.getArticleName().replace("'","\\'"))
                        .replace("<articleUrl>",data.getArticleUrl().replace("'","\\'"))
                        .replace("<searchKeyword>",data.getSearchKeyword().replace("'","\\'"))
                        .replace("<md5>",data.getMd5() + "");


                try {
                    stmt.executeUpdate(sql);
                }catch (Exception e) {
                    logger.info("insert sql:"+sql);
                    e.printStackTrace();
                }
            }
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static String getAllAuthorInfoMD5() throws SQLException {
        String sql = "SELECT md5 FROM authorInfo";
        String databaseHost = Config.mysqlHost;//
        String databaseName = Config.mysqlDatabaseName;// 已经在MySQL数据库中创建好的数据库。
        String userName = Config.mysqlUserName;// MySQL默认的root账户名
        String password = Config.mysqlPassword;// 默认的root账户密码为空
        String url ="jdbc:mysql://"+databaseHost+"/"    //autoReconnect=true&characterEncoding=utf-8
                +databaseName+"?user="+userName+"&password="+password+"&autoReconnect=true&characterEncoding=UTF-8";
        Connection conn = DriverManager.getConnection(url);
        Statement st = conn.createStatement();
        ResultSet rl = st.executeQuery(sql);
        StringBuilder sb = new StringBuilder();
        while (rl.next()){
            sb.append(rl.getString("md5") + ",");
        }
        return sb.toString();
    }

    //获取需要采集的关键词
    public static List<String> querySearchKeyword() throws SQLException {
        String sql = "SELECT `keyword`,`topic` FROM `cnki_keywords` where `language`='en' AND mark = '-1'";
        //String sql = "SELECT `keyword`,`topic` FROM `cnki_keywords` where `language`='en'";
        String databaseHost = Config.mysqlHost;//
        String databaseName = Config.mysqlDatabaseName;// 已经在MySQL数据库中创建好的数据库。
        String userName = Config.mysqlUserName;// MySQL默认的root账户名
        String password = Config.mysqlPassword;// 默认的root账户密码为空
        String url ="jdbc:mysql://"+databaseHost+"/"    //autoReconnect=true&characterEncoding=utf-8
                +databaseName+"?user="+userName+"&password="+password+"&autoReconnect=true&characterEncoding=UTF-8&useSSL=false";
        Connection conn = DriverManager.getConnection(url);
        Statement st = conn.createStatement();
        ResultSet rl = st.executeQuery(sql);
        List<String> keywordList = new ArrayList<>();
        while (rl.next()){
            keywordList.add(rl.getString("keyword"));
            ScholarPaperListStartMain.topicName = rl.getString("topic");
        }
        //使需要采集的关键词变为等待采集状态
        sql = "update `cnki_keywords` set mark = '0' where mark = '-1' and `language`='en'";
        st.executeUpdate(sql);
        st.close();
        conn.close();
        return keywordList;
    }

    //改变采集采集关键词的采集状态
    public static void updateSearchKeywordStatus(String keyword,int status) throws SQLException {
        String sql = "update `cnki_keywords` set mark = '" + (status + 1) + "' where mark='"+ status +"' and keyword='" + keyword + "' and `language`='en'";
        String databaseHost = Config.mysqlHost;//
        String databaseName = Config.mysqlDatabaseName;// 已经在MySQL数据库中创建好的数据库。
        String userName = Config.mysqlUserName;// MySQL默认的root账户名
        String password = Config.mysqlPassword;// 默认的root账户密码为空
        String url ="jdbc:mysql://"+databaseHost+"/"    //autoReconnect=true&characterEncoding=utf-8
                +databaseName+"?user="+userName+"&password="+password+"&autoReconnect=true&characterEncoding=UTF-8&useSSL=false";
        Connection conn = DriverManager.getConnection(url);
        Statement st = conn.createStatement();
        st.executeUpdate(sql);
        st.close();
        conn.close();
    }
}
